package lormi.meipin.common;

import lormi.meipin.dto.PositionSearchDto;
import lormi.meipin.dto.TalentSearchDto;

import java.util.ArrayList;
import java.util.List;

public class SQLJoint {

//===================================收索个人sql
	//查询全国
	private final static String allCityForTalent =
			"SELECT t.memid AS id,p.name,t.photo,if(p.id>7,p.parentid,p.id) AS positionclass,t.workingage AS experience,t.workingstate,"
			+ "e.salary,c.name AS address,c.id AS cityid,e.updatetime ,"
			+ " (select svalue from systeminfo where skey = CONCAT(\"photo_\",p.parentid)) as positionurl "+
			" FROM expectwork e , talent t , city c, positionclass p"+
			" WHERE ((e.memid = t.memid) AND (c.cityid = (select city.cityid from city where city.id = e.hostcity) and c.level = 'city') "
			+ "AND (p.id = e.positionclass))";
	//查询一个城市
	private final static String oneCityForTalent =
			"SELECT t.memid AS id,p.name,t.photo,if(p.id>7,p.parentid,p.id) AS positionclass,t.workingage AS experience,t.workingstate,"
			+ "e.salary,c.name AS address,c.id AS cityid,e.updatetime ,"
			+ " (select svalue from systeminfo where skey = CONCAT(\"photo_\",p.parentid)) as positionurl "+
			" FROM expectwork e , talent t , city c, positionclass p"+
			" WHERE ((e.memid = t.memid) AND (e.hostcity = c.id) "
			+ "AND (p.id = e.positionclass)) ";
	
	public static String getSearchTalentSql(TalentSearchDto talentSearchDto){

		List<String> tem = new ArrayList<>();
		StringBuffer resql = new StringBuffer();
		
		//城市 筛选
		if (talentSearchDto.getCityid()==null || talentSearchDto.getCityid()==0) {//全国
			tem.add(allCityForTalent);
		}else {//一个城市
			tem.add(oneCityForTalent);
			tem.add(" c.cityid = (select cityid from city where city.id = "+talentSearchDto.getCityid()+" ) ");
		}
		
		//职位筛选
		if (talentSearchDto.getPositionclass()!=null) {
			tem.add(" if("+talentSearchDto.getPositionclass()+">7,p.id,p.parentid) = "+talentSearchDto.getPositionclass()+" ");
		}
		
		//薪资 筛选
		if (talentSearchDto.getSalary()!=null && (talentSearchDto.getSalary()>0 && talentSearchDto.getSalary()<6)) {
			int[] s = StaticEnum.getSalaryLevel(talentSearchDto.getSalary());
			tem.add(" (e.salary >= "+s[0]+" and "+s[1]+" >= e.salary) ");
		}
		
		//经验 筛选
		if (talentSearchDto.getExperience()!=null && (talentSearchDto.getExperience()>0 && talentSearchDto.getExperience()<5)) {
			int[] e = StaticEnum.getExperienceLevel(talentSearchDto.getExperience());
			tem.add(" (t.workingage >= "+e[0]+" and "+e[1]+" >= t.workingage) ");
		}
		resql.append(tem.get(0));
		if (tem.size()>1) {
			for (int i=1;i<tem.size();i++) {
				resql.append(" and "+tem.get(i));
			}
		}
		return resql.toString();
	}
	
	
	
	//===================================收索商家职位sql
	//查询全国
	private final static String allCityForPosition =
			"SELECT p.id,p.merid,p.hostcity AS cityid,p.hidden,mc.photo AS pictureurl,p.title AS title,"+
		        "mc.company,p.salarymin,p.salarymax,c.name AS cityname,pc.parentid AS positionclass,"+
		        "p.positionclass AS positionid,p.experience,p.updatetime AS createtime ,"
		        + " (select svalue from systeminfo where skey = CONCAT(\"photo_\",pc.parentid)) as positionurl "+
		    " FROM position p , merchant mc, city c , positionclass pc"+
			" where (p.merid=mc.merid ) and p.positionclass=pc.id and"+
		    	" (c.cityid = (select cityid from city where city.id = p.hostcity) and c.level = 'city')"
		    	+ " and p.hidden = 1";
	//查询一个城市
	private final static String oneCityForPosition =
			"SELECT p.id,p.merid,p.hostcity AS cityid,p.hidden,mc.photo AS pictureurl,p.title AS title,"+
		        "mc.company,p.salarymin,p.salarymax,c.name AS cityname,pc.parentid AS positionclass,"+
		        "p.positionclass AS positionid,p.experience,p.updatetime AS createtime ,"
		        + " (select svalue from systeminfo where skey = CONCAT(\"photo_\",pc.parentid)) as positionurl "+
		    " FROM position p , merchant mc , city c , positionclass pc"+
			" where (p.merid=mc.merid ) and (p.positionclass=pc.id) and (p.hostcity = c.id )"
			+ " and p.hidden = 1";
	
	public static String getSearchPositionSql(PositionSearchDto positionSearchDto){

		List<String> tem = new ArrayList<>();
		StringBuffer resql = new StringBuffer();
		
		//城市 筛选
		if (positionSearchDto.getCityid()==null || positionSearchDto.getCityid()==0) {//全国
			tem.add(allCityForPosition);
		}else {//一个城市
			tem.add(oneCityForPosition);
			tem.add(" c.cityid = (select cityid from city where city.id = "+positionSearchDto.getCityid()+" ) ");
		}
		
		//职位筛选
		if (positionSearchDto.getPositionclass()!=null) {
			tem.add(" if("+positionSearchDto.getPositionclass()+">7,pc.id,pc.parentid) = "+positionSearchDto.getPositionclass()+" ");
		}
		
		//薪资 筛选
		if (positionSearchDto.getSalary()!=null && (positionSearchDto.getSalary()>0 && positionSearchDto.getSalary()<6)) {
			int[] s = StaticEnum.getSalaryLevel(positionSearchDto.getSalary());
			tem.add(" (p.salarymax >= "+s[0]+" and "+s[1]+" >= p.salarymin) ");
		}
		
		//经验 筛选
		if (positionSearchDto.getExperience()!=null && (positionSearchDto.getExperience()>0 && positionSearchDto.getExperience()<5)) {
			int[] e = StaticEnum.getExperienceLevel(positionSearchDto.getExperience());
			tem.add(" (p.experience >= "+e[0]+" and "+e[1]+" >= p.experience) ");
		}
		resql.append(tem.get(0));
		if (tem.size()>1) {
			for (int i=1;i<tem.size();i++) {
				resql.append(" and "+tem.get(i));
			}
		}
		return resql.toString();
	}
	
}
